<?php
class Reports extends Model{
	
    /* Report Main Deal */
    function getDeal($month,$year){
        $sql =" SELECT ";
	    $sql.=" username,";
	    $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_posting)='".$month."' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS total, ";
	    $sql.=" ( ";
		$sql.=" SELECT SUM(target) ";
		$sql.=" FROM jobs_target_head ";
		$sql.=" JOIN jobs_target_details ON jobs_target_details.job_id=jobs_target_head.id ";
		$sql.=" WHERE jobs_target_details.username=users.username ";
		$sql.=" AND jobs_target_head.month='".$month."' ";
		$sql.=" AND jobs_target_head.year='".$year."' ";
	    $sql.=" ) AS target	";
        $sql.=" FROM users ";
        $sql.=" WHERE ";
	    $sql.=" pos_id =".$this->config->item('ta');
        $sql.=" AND group_id =".$this->config->item('user');
	    $sql.=" AND enabled =1 AND sales=1 ";
        $sql.=" ORDER BY user_no ASC ";
        $q=$this->db->query($sql);
        return $rows=$q->result_array();
    }
    
    function getDealMonthly($month_1,$month_2,$month_3,$year){
        $sql =" SELECT ";
	    $sql.=" username,";
        
	    $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_posting)='".$month_1."' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS month_1, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_posting)='".$month_2."' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS month_2, ";
	    
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_posting)='".$month_3."' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS month_3 ";
        
        $sql.=" FROM users ";
        $sql.=" WHERE ";
	    $sql.=" pos_id =".$this->config->item('ta');
        $sql.=" AND group_id =".$this->config->item('user');
	    $sql.=" AND enabled =1 AND sales=1 ";
        $sql.=" ORDER BY user_no ASC ";
        $q=$this->db->query($sql);
        return $rows=$q->result_array();
    }
    
    function getDealYearly($year){
        $sql =" SELECT ";
	    $sql.=" username,";
        
	    $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_posting)='01' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS month_1, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_posting)='02' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS month_2, ";
	    
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_posting)='03' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS month_3, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_posting)='04' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS month_4, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_posting)='05' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS month_5, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_posting)='06' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS month_6, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_posting)='07' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS month_7, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_posting)='08' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS month_8, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_posting)='09' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS month_9, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_posting)='10' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS month_10, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_posting)='11' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS month_11, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_posting)='12' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS month_12 ";
        
        $sql.=" FROM users ";
        $sql.=" WHERE ";
	    $sql.=" pos_id =".$this->config->item('ta');
        $sql.=" AND group_id =".$this->config->item('user');
	    $sql.=" AND enabled =1 AND sales=1 ";
        $sql.=" ORDER BY user_no ASC ";
        $q=$this->db->query($sql);
        return $rows=$q->result_array();
    }
    /* Report Main Deal */
    
    
    /* Report Main Real */
    function getReal($month,$year){
        $sql =" SELECT ";
	    $sql.=" username,";
	    $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_in)='".$month."' ";
		$sql.=" AND YEAR(date_in)='".$year."' "; 
	    $sql.=" )AS total, ";
	    $sql.=" ( ";
		$sql.=" SELECT SUM(target) ";
		$sql.=" FROM jobs_target_head ";
		$sql.=" JOIN jobs_target_details ON jobs_target_details.job_id=jobs_target_head.id ";
		$sql.=" WHERE jobs_target_details.username=users.username ";
		$sql.=" AND jobs_target_head.month='".$month."' ";
		$sql.=" AND jobs_target_head.year='".$year."' ";
	    $sql.=" ) AS target	";
        $sql.=" FROM users ";
        $sql.=" WHERE ";
	    $sql.=" pos_id =".$this->config->item('ta');
        $sql.=" AND group_id =".$this->config->item('user');
	    $sql.=" AND enabled =1 AND sales=1 ";
        $sql.=" ORDER BY user_no ASC ";
        $q=$this->db->query($sql);
        return $rows=$q->result_array();
    }
    
    function getRealMonthly($month_1,$month_2,$month_3,$year){
        $sql =" SELECT ";
	    $sql.=" username,";
        
	    $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_in)='".$month_1."' ";
		$sql.=" AND YEAR(date_in)='".$year."' "; 
	    $sql.=" )AS month_1, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_in)='".$month_2."' ";
		$sql.=" AND YEAR(date_in)='".$year."' "; 
	    $sql.=" )AS month_2, ";
	    
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_in)='".$month_3."' ";
		$sql.=" AND YEAR(date_in)='".$year."' "; 
	    $sql.=" )AS month_3 ";
        
        $sql.=" FROM users ";
        $sql.=" WHERE ";
	    $sql.=" pos_id =".$this->config->item('ta');
        $sql.=" AND group_id =".$this->config->item('user');
	    $sql.=" AND enabled =1 AND sales=1 ";
        $sql.=" ORDER BY user_no ASC ";
        $q=$this->db->query($sql);
        return $rows=$q->result_array();
    }
    
    function getRealYearly($year){
        $sql =" SELECT ";
	    $sql.=" username,";
        
	    $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_posting)='01' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS month_1, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_in)='02' ";
		$sql.=" AND YEAR(date_in)='".$year."' "; 
	    $sql.=" )AS month_2, ";
	    
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_in)='03' ";
		$sql.=" AND YEAR(date_in)='".$year."' "; 
	    $sql.=" )AS month_3, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_in)='04' ";
		$sql.=" AND YEAR(date_in)='".$year."' "; 
	    $sql.=" )AS month_4, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_in)='05' ";
		$sql.=" AND YEAR(date_in)='".$year."' "; 
	    $sql.=" )AS month_5, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_in)='06' ";
		$sql.=" AND YEAR(date_in)='".$year."' "; 
	    $sql.=" )AS month_6, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_in)='07' ";
		$sql.=" AND YEAR(date_in)='".$year."' "; 
	    $sql.=" )AS month_7, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_in)='08' ";
		$sql.=" AND YEAR(date_in)='".$year."' "; 
	    $sql.=" )AS month_8, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_in)='09' ";
		$sql.=" AND YEAR(date_in)='".$year."' "; 
	    $sql.=" )AS month_9, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_in)='10' ";
		$sql.=" AND YEAR(date_in)='".$year."' "; 
	    $sql.=" )AS month_10, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_in)='11' ";
		$sql.=" AND YEAR(date_in)='".$year."' "; 
	    $sql.=" )AS month_11, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
		$sql.=" AND MONTH(date_in)='12' ";
		$sql.=" AND YEAR(date_in)='".$year."' "; 
	    $sql.=" )AS month_12 ";
        
        $sql.=" FROM users ";
        $sql.=" WHERE ";
	    $sql.=" pos_id =".$this->config->item('ta');
        $sql.=" AND group_id =".$this->config->item('user');
	    $sql.=" AND enabled =1 AND sales=1 ";
        $sql.=" ORDER BY user_no ASC ";
        $q=$this->db->query($sql);
        return $rows=$q->result_array();
    }
    /* Report Main Real */
    
    /* Report Main Type */
    function getType($month,$year){
        $sql =" SELECT ";
	    $sql.=" username,";
	    
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
        $sql.=" AND order_type='Repeat Order' ";
		$sql.=" AND MONTH(date_posting)='".$month."' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS repeat_, ";
	    
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
        $sql.=" AND order_type='First Order' ";
		$sql.=" AND MONTH(date_posting)='".$month."' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS first_ ";
	   
       
        $sql.=" FROM users ";
        $sql.=" WHERE ";
	    $sql.=" pos_id =".$this->config->item('ta');
        $sql.=" AND group_id =".$this->config->item('user');
	    $sql.=" AND enabled =1 AND sales=1 ";
        $sql.=" ORDER BY user_no ASC ";
        $q=$this->db->query($sql);
        return $rows=$q->result_array();
    }
    /* Report Main Type */
    
    /* Report Main Type */
    function getTypeMonthly($month1,$month2,$month3,$year){
        $sql =" SELECT ";
	    $sql.=" ";
	    
        $sql.=" ( ";
		$sql.=" SELECT SUM((p.invest - (jd.discount)) + (p.invest * jd.ppn / 100)  ) ";
		$sql.=" FROM jobs jd ";
		$sql.=" JOIN products p ON p.id=jd.product_id ";
		$sql.=" JOIN customers c ON c.id=jd.customer_id ";
		$sql.=" WHERE jd.id=jk.id ";
        $sql.=" AND jd.order_type='Repeat Order' ";
		$sql.=" AND MONTH(jd.date_posting)='".$month1."' ";
		$sql.=" AND YEAR(jd.date_posting)='".$year."' "; 
	    $sql.=" )AS repeatmonth1, ";
	    
        $sql.=" ( ";
		$sql.=" SELECT SUM((p.invest - (jd.discount)) + (p.invest * jd.ppn / 100)  ) ";
		$sql.=" FROM jobs jd ";
		$sql.=" JOIN products p ON p.id=jd.product_id ";
		$sql.=" JOIN customers c ON c.id=jd.customer_id ";
		$sql.=" WHERE jd.id=jk.id ";
        $sql.=" AND jd.order_type='First Order' ";
		$sql.=" AND MONTH(jd.date_posting)='".$month1."' ";
		$sql.=" AND YEAR(jd.date_posting)='".$year."' "; 
	    $sql.=" )AS firstmonth1, ";
        //
        $sql.=" ( ";
		$sql.=" SELECT SUM( (p.invest - (jd.discount) ) + (p.invest * jd.ppn / 100)  ) ";
		$sql.=" FROM jobs jd ";
		$sql.=" JOIN products p ON p.id=jd.product_id ";
		$sql.=" JOIN customers c ON c.id=jd.customer_id ";
		$sql.=" WHERE jd.id=jk.id ";
        $sql.=" AND jd.order_type='Repeat Order' ";
		$sql.=" AND MONTH(jd.date_posting)='".$month2."' ";
		$sql.=" AND YEAR(jd.date_posting)='".$year."' "; 
	    $sql.=" )AS repeatmonth2, ";
	    
        $sql.=" ( ";
		$sql.=" SELECT SUM((p.invest - (jd.discount)) + (p.invest * jd.ppn / 100)  ) ";
		$sql.=" FROM jobs jd ";
		$sql.=" JOIN products p ON p.id=jd.product_id ";
		$sql.=" JOIN customers c ON c.id=jd.customer_id ";
		$sql.=" WHERE jd.id=jk.id ";
        $sql.=" AND jd.order_type='First Order' ";
		$sql.=" AND MONTH(jd.date_posting)='".$month2."' ";
		$sql.=" AND YEAR(jd.date_posting)='".$year."' "; 
	    $sql.=" )AS firstmonth2, ";
        
        $sql.=" ( ";
		$sql.=" SELECT SUM((p.invest - (jd.discount)) + (p.invest * jd.ppn / 100)  ) ";
		$sql.=" FROM jobs jd ";
		$sql.=" JOIN products p ON p.id=jd.product_id ";
		$sql.=" JOIN customers c ON c.id=jd.customer_id ";
		$sql.=" WHERE jd.id=jk.id ";
        $sql.=" AND jd.order_type='Repeat Order' ";
		$sql.=" AND MONTH(date_posting)='".$month3."' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS repeatmonth3, ";
	    
        $sql.=" ( ";
		$sql.=" SELECT SUM((p.invest - (jd.discount)) + (p.invest * jd.ppn / 100)  ) ";
		$sql.=" FROM jobs jd";
		$sql.=" JOIN products p ON p.id=jd.product_id ";
		$sql.=" JOIN customers c ON c.id=jd.customer_id ";
		$sql.=" WHERE jd.id=jk.id ";
        $sql.=" AND jd.order_type='First Order' ";
		$sql.=" AND MONTH(jd.date_posting)='".$month3."' ";
		$sql.=" AND YEAR(jd.date_posting)='".$year."' "; 
	    $sql.=" )AS firstmonth3 ";
	   
       
        $sql.=" FROM jobs jk ";
        $sql.=" pos_id =".$this->config->item('ta');
        $sql.=" AND group_id =".$this->config->item('user');
	    $sql.=" AND enabled =1 AND sales=1 ";
        $sql.=" ORDER BY user_no ASC ";
        $q=$this->db->query($sql);
        return $rows=$q->result_array();
    }
    /* Report Main Type */
    
    /* Report Main Incoming */
    function getIncoming($month,$year){
        $sql =" SELECT ";
	    $sql.=" username,";
	    
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
        $sql.=" AND coming_type='Incoming' ";
		$sql.=" AND MONTH(date_posting)='".$month."' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS in_, ";
	    
        $sql.=" ( ";
		$sql.=" SELECT SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100)  ) ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic=users.username ";
        $sql.=" AND coming_type='Non Incoming' ";
		$sql.=" AND MONTH(date_posting)='".$month."' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
	    $sql.=" )AS non_ ";
	   
       
        $sql.=" FROM users ";
        $sql.=" WHERE ";
	    $sql.=" pos_id =".$this->config->item('ta');
        $sql.=" AND group_id =".$this->config->item('user');
	    $sql.=" AND enabled =1 AND sales=1 ";
        $sql.=" ORDER BY user_no ASC ";
        $q=$this->db->query($sql);
        return $rows=$q->result_array();
    }
    /* Report Main Incoming */
    
    /* Report Main Product */
    function getProducts(){
        $sql ="SELECT * FROM products ps ";
		$query=$this->db->query($sql);
		return $query->result_array();
    }
    
    function getUserP(){
		$sql =" SELECT * FROM users ";
        $sql.=" WHERE pos_id =".$this->config->item('ta');
        $sql.=" AND group_id =".$this->config->item('user');
	    $sql.=" AND enabled =1 AND sales=1 ";
        $sql.=" ORDER BY user_no ASC ";
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getJobProducts($user,$product,$month,$year)
    {
		$sql =" SELECT *,(SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100))) AS total ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic='".$user."'";
        $sql.=" AND products.id='".$product."'";
		$sql.=" AND MONTH(date_posting)='".$month."' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
        $q=$this->db->query($sql);
        return $rows=$q->row_array();
    }
    
    function getJobProductTotal($product,$month,$year)
    {
		$sql =" SELECT *,(SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100))) AS total ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
        $sql.=" WHERE products.id='".$product."'";
		$sql.=" AND MONTH(date_posting)='".$month."' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
        $q=$this->db->query($sql);
        return $rows=$q->row_array();
    }
    
    /* Report Main Product */
    
    /* Report Main Sector */
    function getJobSectors($user,$sector,$month,$year)
    {
		$sql =" SELECT *,(SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100))) AS total ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
		$sql.=" WHERE jobs.pic='".$user."'";
        $sql.=" AND customers.sector_id='".$sector."'";
		$sql.=" AND MONTH(date_posting)='".$month."' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
        $q=$this->db->query($sql);
        return $rows=$q->row_array();
    }
    
    function getJobSectorTotal($sector,$month,$year)
    {
		$sql =" SELECT *,(SUM((products.invest - (jobs.discount)) + (products.invest * jobs.ppn / 100))) AS total ";
		$sql.=" FROM jobs ";
		$sql.=" JOIN products ON products.id=jobs.product_id ";
		$sql.=" JOIN customers ON customers.id=jobs.customer_id ";
        $sql.=" WHERE customers.sector_id='".$sector."'";
		$sql.=" AND MONTH(date_posting)='".$month."' ";
		$sql.=" AND YEAR(date_posting)='".$year."' "; 
        $q=$this->db->query($sql);
        return $rows=$q->row_array();
    }
    /* Report Main Sector */
    
	function getSectors(){
		$sql ="SELECT * FROM customer_sector cs ";
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getUsers(){
		$sql ="SELECT * FROM users ";
        $sql.=" WHERE pos_id =".$this->config->item('ta');
        $sql.=" AND group_id =".$this->config->item('user');
	    $sql.=" AND enabled =1 AND sales=1 ";
        $sql.=" ORDER BY user_no ASC ";
		$query=$this->db->query($sql);
		return $query->result_array();
	}
	
	function getGroups(){
		$sql ="SELECT * FROM customer_group ";
		$query=$this->db->query($sql);
		return $query->result_array();
	}
	
	function getSources(){
		$sql ="SELECT * FROM customer_source csr ";
		$query=$this->db->query($sql);
		return $query->result_array();
	}
	
	function getStatus(){
		$sql ="SELECT * FROM customer_status ";
		$query=$this->db->query($sql);
		return $query->result_array();
	}
}	